package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.kcs.common.utils.Constant;
import com.kcs.fxmargin.bo.ImportRRAndMMBO;

public class ImportRRAndMMDAO {

	private Logger log = Logger.getLogger(Constant.LOG_FX);

	public ImportRRAndMMBO getImportRRAndMMList(ImportRRAndMMBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<ImportRRAndMMBO> resultList = null;
		ImportRRAndMMBO rm = null;
		
		log.info("Begin : ImportRRAndMMDAO : getImportRRAndMMList");
		
		try{
			sql = "with order_lst as ( "+
				  " select 'TODAY' period, 1 p_order "+
				  " union select 'TOM', 2 p_order "+
				  " union select 'SPOT', 3 p_order "+
				  " union select '1W', 4 p_order "+
				  " union select '2W', 5 p_order "+
				  " union select '1M', 6 p_order "+
				  " union select '2M', 7 p_order "+
				  " union select '3M', 8 p_order "+
				  " union select '6M', 9 p_order "+
				  " union select '9M', 10 p_order "+
				  " union select '1Y', 11 p_order "+
				  " union select '2Y', 12 p_order "+
				  " ) "+
				  " select cast(h.time as varchar(8)) time, d.curr1+d.curr2 curr, d.period, d.rate "+
				  " from t_rate_header h, t_rate_detail d left join order_lst l on d.period=l.period "+
				  " where h.active_status='A' "+
				  " and d.active_status='A' "+
				  " and h.id=d.rate_header_id "+
				  " and h.as_of_date=? "+
				  " order by time desc, curr1, p_order ";
		
			log.info(sql);
			log.info(">asOfDate: "+bo.getInAsOfDate());
			
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bo.getInAsOfDate());
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<ImportRRAndMMBO>();
				
				while(rs.next()){
					rm = new ImportRRAndMMBO();
					rm.setTime(rs.getString("time"));
					rm.setCurr(rs.getString("curr"));
					rm.setPeriod(rs.getString("period"));
					rm.setRate(rs.getString("rate"));
					resultList.add(rm);
				}
				
				bo.setImportRrAndMmList(resultList);
			}
			
			log.info("resulst list size = "+((resultList==null)?"null":resultList.size()));
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		log.info("End : ImportRRAndMMDAO : getImportRRAndMMList");
		return bo;
	}

}
